export const DB = {
	dbName: 'inspection', // 数据库名称
	dbPath: '_downloads/inspection.db', // 数据库地址,推荐以下划线为开头   _doc/xxx.db

	// 判断数据库是否打开
	isOpen() {
		// 数据库打开了就返回 true,否则返回 false
		var open = plus.sqlite.isOpenDatabase({
			name: this.dbName, // 数据库名称
			path: this.dbPath // 数据库地址
		})
		return open;
	},

	// 创建数据库 或 有该数据库就打开
	openSqlite() {
		return new Promise((resolve, reject) => {
			// 打开数据库
			plus.sqlite.openDatabase({
				name: this.dbName,
				path: this.dbPath,
				success(e) {
					resolve(e); // 成功回调
				},
				fail(e) {
					reject(e); // 失败回调
				}
			})
		})
	},

	// 关闭数据库
	closeSqlite() {
		return new Promise((resolve, reject) => {
			plus.sqlite.closeDatabase({
				name: this.dbName,
				success(e) {
					resolve(e);
				},
				fail(e) {
					reject(e);
				}
			})
		})
	},

	// 数据库建表 sql:'CREATE TABLE IF NOT EXISTS dbTable("id" varchar(50),"name" TEXT) 
	// 创建 CREATE TABLE IF NOT EXISTS 、 dbTable 是表名，不能用数字开头、括号里是表格的表头
	createTable(dbTable, data) {
		return new Promise((resolve, reject) => {
			// executeSql: 执行增删改等操作的SQL语句
			plus.sqlite.executeSql({
				name: this.dbName,
				sql: `CREATE TABLE IF NOT EXISTS ${dbTable}(${data})`,
				success(e) {
					resolve(e);
				},
				fail(e) {
					reject(e);
				}
			})
		})
	},

	// 数据库删表 sql:'DROP TABLE dbTable'
	dropTable(dbTable) {
		return new Promise((resolve, reject) => {
			plus.sqlite.executeSql({
				name: this.dbName,
				sql: `DROP TABLE ${dbTable}`,
				success(e) {
					resolve(e);
				},
				fail(e) {
					reject(e);
				}
			})
		})
	},

	// 向表格里添加数据 sql:'INSERT INTO dbTable VALUES('x','x','x')'   对应新增
	// 或者 sql:'INSERT INTO dbTable ('x','x','x') VALUES('x','x','x')'   具体新增
	// 插入 INSERT INTO  、 dbTable 是表名、根据表头列名插入列值
	insertTableData(dbTable, data, condition) {
		// 判断有没有传参
		if (dbTable !== undefined && data !== undefined) {
			// 判断传的参是否有值
			var bol = (JSON.stringify(data) == "{}");
			if (!bol) {
				if (condition == undefined) {
					var sql = `INSERT INTO ${dbTable} VALUES('${data}')`;
				} else {
					var sql = `INSERT INTO ${dbTable} (${condition}) VALUES(${data})`;
				}
				// console.log(sql);
				return new Promise((resolve, reject) => {
					// 表格添加数据
					plus.sqlite.executeSql({
						name: this.dbName,
						sql: sql,
						success(e) {
							resolve(e);
						},
						fail(e) {
							reject(e);
						}
					})
				})
			} else {
				return new Promise((resolve, reject) => {
					reject("错误添加")
				})
			}
		} else {
			return new Promise((resolve, reject) => {
				reject("错误添加")
			})
		}
	},

	// 根据条件向表格里添加数据  有数据更新、无数据插入
	// (建表时需要设置主键) 例如 --- "roomid" varchar(50) PRIMARY KEY
	insertOrReplaceData(dbTable, data, condition) {
		// 判断有没有传参
		if (dbTable !== undefined && data !== undefined) {
			// if (condition == undefined) {
			// 	var sql = `INSERT OR REPLACE INTO ${dbTable} VALUES('${data}')`;
			// } else {
				var sql = `INSERT OR REPLACE INTO ${dbTable} (${condition}) VALUES(${data})`;
			// }
			console.log(sql);
			return new Promise((resolve, reject) => {
				// 表格添加数据
				plus.sqlite.executeSql({
					name: this.dbName,
					sql: sql,
					success(e) {
						console.log(e)
						reject(e);
						
						// if(e.rows.length > 0){
						// 	plus.sqlite.executeSql({
						// 		name: this.dbName,
						// 		sql: sql,
						// 		success(e) {
						// 			resolve(e);
						// 		},
						// 		fail(e) {
						// 			reject(e);
						// 		}
						// 	})
						// }else{
						// 	plus.sqlite.executeSql({
						// 		name: this.dbName,
						// 		sql: sql,
						// 		success(e) {
						// 			resolve(e);
						// 		},
						// 		fail(e) {
						// 			reject(e);
						// 		}
						// 	})
						// }
						
					},
					fail(e) {
						console.log(e)
						reject(e);
					}
				})
			})
		} else {
			return new Promise((resolve, reject) => {
				reject("错误添加")
			})
		}
	},

	selectTableCpountNew(dbTable, where) {
		if (dbTable !== undefined) {
			var sql = '';
			if (where == undefined) {
				console.log('sql');
				sql = `SELECT count(*) as count FROM ${dbTable} `;
			} else {
				sql =
					`SELECT count(*) as count FROM ${dbTable} WHERE ${where}`;
			}


			console.log(sql);


			return new Promise((resolve, reject) => {
				// 表格查询数据  执行查询的SQL语句
				plus.sqlite.selectSql({
					name: this.dbName,
					sql: sql,
					success(e) {
						resolve(e);
					},
					fail(e) {
						reject(e);
					}
				})
			})
		} else {
			return new Promise((resolve, reject) => {
				reject("错误查询")
			});
		}
	},


	selectTableDataNew(dbTable, where, order) {
		if (dbTable !== undefined) {
			var sql = '';
			if (where == undefined) {
				console.log('sql');
				sql = `SELECT * FROM ${dbTable} `;
			} else {
				sql =
					`SELECT * FROM ${dbTable} WHERE ${where}`;
			}

			if (order !== undefined) {
				sql = sql + order
			}

			// console.log(sql);


			return new Promise((resolve, reject) => {
				// 表格查询数据  执行查询的SQL语句
				plus.sqlite.selectSql({
					name: this.dbName,
					sql: sql,
					success(e) {
						resolve(e);
					},
					fail(e) {
						reject(e);
					}
				})
			})
		} else {
			return new Promise((resolve, reject) => {
				reject("错误查询")
			});
		}
	},



	// 查询获取数据库里的数据 sql:'SELECT * FROM dbTable WHERE lname = 'lvalue''
	// 查询 SELECT * FROM 、 dbTable 是表名、 WHERE 查找条件 lname,lvalue 是查询条件的列名和列值
	selectTableData(dbTable, from1, from_id, to1, to_id, where1, where_id1, where_no1, where_no_id1) {
		if (dbTable !== undefined) {

			if (from_id != undefined && from1 != undefined && to_id != undefined && to1 != undefined) {
				// 一个检索条件
				var sql =
					`SELECT * FROM ${dbTable} WHERE (${from1} = '${from_id}' AND ${to1} = '${to_id}' or ${from1} = '${to_id}' AND ${to1} = '${from_id}') and typeu != 7`;

			} else if (from_id != undefined && to1 != undefined) {
				// 一个检索条件
				var sql =
					`SELECT * FROM ${dbTable} WHERE (${from1} = '${from_id}' ) or ( ${to1} = '${from_id}')  and typeu != 7`;

			} else if (from_id != undefined) {
				// 一个检索条件
				var sql =
					`SELECT * FROM ${dbTable} WHERE (${from1} = '${from_id}'  and typeu != 7`;
			} else {
				var sql =
					`SELECT * FROM ${dbTable} `;

			}
			if (where1 != undefined && where_id1 != undefined) {
				// 一个检索条件
				sql = sql +
					` and  ${where1} = '${where_id1}')`;
			}
			if (where_no1 != undefined && where_no_id1 != undefined) {
				// 一个检索条件
				sql = sql +
					` and ${where_no1} != '${where_no_id1}')`;
			}

			if (dbTable.indexOf('chat') >= 0) {
				sql = sql + ` and del_flag = 0`;
			}
			if (dbTable.indexOf('friend') >= 0) {
				sql = sql + ` group by user_uid`;
			}

			return new Promise((resolve, reject) => {
				// 表格查询数据  执行查询的SQL语句
				plus.sqlite.selectSql({
					name: this.dbName,
					sql: sql,
					success(e) {
						resolve(e);
					},
					fail(e) {
						reject(e);
					}
				})
			})
		} else {
			return new Promise((resolve, reject) => {
				reject("错误查询")
			});
		}
	},

	selectGroupmessage(dbTable, where1, where_id1, where_no1, where_no_id1) {
		if (dbTable !== undefined) {


			// 一个检索条件
			var sql =
				`SELECT * FROM ${dbTable} WHERE ${where1} = '${where_id1}' and del_flag = 0 `;

			if (where_no1 != undefined && where_no_id1 != undefined) {
				// 一个检索条件
				sql = sql +
					` and ${where_no1} != '${where_no_id1}')`;
			}
			console.log(sql);
			return new Promise((resolve, reject) => {
				// 表格查询数据  执行查询的SQL语句
				plus.sqlite.selectSql({
					name: this.dbName,
					sql: sql,
					success(e) {
						resolve(e);
					},
					fail(e) {
						reject(e);
					}
				})
			})
		} else {
			return new Promise((resolve, reject) => {
				reject("错误查询")
			});
		}
	},

	selectmessageData(dbTable, to1, from_id) {
		if (dbTable !== undefined) {

			if (from_id != undefined) {
				var sql = `SELECT *,(
					    SELECT count(*)
					    WHERE (t1.${to1} = '${from_id}' )
						and (t1.is_read is null  or t1.is_read = 0)
						and del_flag = 0
					) as unread_count
					FROM ${dbTable} AS t1
					WHERE id IN (
					    SELECT MAX(t2.id)
					    FROM ${dbTable} AS t2
					    WHERE t2.${to1} = '${from_id}'
						and (t2.is_read is null  or t2.is_read = 0)
						and del_flag = 0
					    GROUP BY t2.relatedId
					)
					ORDER BY id DESC`;

				// console.log(sql), "-=-=-=-=0-0-0-088";
			}
			return new Promise((resolve, reject) => {
				// 表格查询数据  执行查询的SQL语句
				plus.sqlite.selectSql({
					name: this.dbName,
					sql: sql,
					success(e) {
						resolve(e);
					},
					fail(e) {
						reject(e);
					}
				})
			})
		} else {
			return new Promise((resolve, reject) => {
				reject("错误查询")
			});
		}
	},
	selectAllmessageData(dbTable, to1, from_id, from1) {
		if (dbTable !== undefined) {

			if (from_id != undefined) {
				var sql = `SELECT *,(SELECT count(*) WHERE ((t1.${to1} = '${from_id}' or t1.${from1} = '${from_id}') and t1.typeu != 7 ) and (t1.is_read is null  or t1.is_read = 0) and del_flag = 0 and ${from1} !=  '${from_id}') as unread_count
					FROM ${dbTable} AS t1
					WHERE id IN (
					    SELECT MAX(t2.id)
					    FROM ${dbTable} AS t2
					    WHERE ((t2.${to1} = '${from_id}' or t2.${from1} = '${from_id}') and t2.typeu != 7 )
						
					    GROUP BY t2.relatedId)
					UNION
						SELECT *,(SELECT count(*) WHERE (t1.${to1} = '${from_id}' and t1.typeu == 7 ) and (t1.is_read is null  or t1.is_read = 0) and del_flag = 0 ) as unread_count
					FROM ${dbTable} AS t1
					WHERE id IN (
					    SELECT MAX(t2.id)
					    FROM ${dbTable} AS t2
					    WHERE (t2.${to1} = '${from_id}' and t2.typeu == 7 )
						and del_flag = 0
					    GROUP BY t2.typeu)
					
					
					ORDER BY is_top DESC,id DESC`;

				// console.log(sql), "-=-=-=-=0-0-0-088";
			}

			return new Promise((resolve, reject) => {
				// 表格查询数据  执行查询的SQL语句
				plus.sqlite.selectSql({
					name: this.dbName,
					sql: sql,
					success(e) {
						resolve(e);
					},
					fail(e) {
						reject(e);
					}
				})
			})
		} else {
			return new Promise((resolve, reject) => {
				reject("错误查询")
			});
		}
	},
	selectsingleTableData(dbTable, to1, from_id, from1) {
		if (dbTable !== undefined) {

			if (from_id != undefined) {
				var sql = `SELECT *,(SELECT count(*) WHERE ((t1.${to1} = '${from_id}' or t1.${from1} = '${from_id}') and t1.typeu != 7 ) and (t1.is_read is null  or t1.is_read = 0) and del_flag = 0  and ${from1} !=  '${from_id}') as unread_count
					FROM ${dbTable} AS t1
					WHERE id IN (
					    SELECT MAX(t2.id)
					    FROM ${dbTable} AS t2
					    WHERE ((t2.${to1} = '${from_id}' or t2.${from1} = '${from_id}') and t2.typeu != 7 and (t2.gid = null or t2.gid = 0 or t2.gid = '0'))
						
					    GROUP BY t2.relatedId)
					UNION
						SELECT *,(SELECT count(*) WHERE (t1.${to1} = '${from_id}' and t1.typeu == 7 ) and (t1.is_read is null  or t1.is_read = 0) and del_flag = 0 ) as unread_count
					FROM ${dbTable} AS t1
					WHERE id IN (
					    SELECT MAX(t2.id)
					    FROM ${dbTable} AS t2
					    WHERE (t2.${to1} = '${from_id}' and t2.typeu == 7 )
						and del_flag = 0
					    GROUP BY t2.typeu)
					
					
					ORDER BY is_top DESC,id DESC`;

				// console.log(sql);
			}

			return new Promise((resolve, reject) => {
				// 表格查询数据  执行查询的SQL语句
				plus.sqlite.selectSql({
					name: this.dbName,
					sql: sql,
					success(e) {
						resolve(e);
					},
					fail(e) {
						reject(e);
					}
				})
			})
		} else {
			return new Promise((resolve, reject) => {
				reject("错误查询")
			});
		}
	},
	selectgroupTableData(dbTable, to1, from_id, from1) {
		if (dbTable !== undefined) {

			if (from_id != undefined) {
				var sql = `SELECT *,(SELECT count(*) WHERE ((t1.${to1} = '${from_id}' or t1.${from1} = '${from_id}') and t1.typeu != 7 ) and (t1.is_read is null  or t1.is_read = 0) and del_flag = 0  ) as unread_count
					FROM ${dbTable} AS t1
					WHERE id IN (
					    SELECT MAX(t2.id)
					    FROM ${dbTable} AS t2
					    WHERE ((t2.${to1} = '${from_id}' or t2.${from1} = '${from_id}') and t2.typeu != 7 and t2.gid != 0)
						
					    GROUP BY t2.relatedId
					)
					
					
					ORDER BY is_top DESC,id DESC`;

				// console.log(sql);
			}

			return new Promise((resolve, reject) => {
				// 表格查询数据  执行查询的SQL语句
				plus.sqlite.selectSql({
					name: this.dbName,
					sql: sql,
					success(e) {
						resolve(e);
					},
					fail(e) {
						reject(e);
					}
				})
			})
		} else {
			return new Promise((resolve, reject) => {
				reject("错误查询")
			});
		}
	},

	selectTabcountData(dbTable, to1, from_id, from1) {
		if (dbTable !== undefined) {

			if (from_id != undefined) {
				var sql = `
					SELECT
					(SELECT count(*) 
										FROM ${dbTable} AS t1
										WHERE ((t1.${to1} = '${from_id}' or t1.${from1} = '${from_id}') and (t1.is_read is null  or t1.is_read = 0) and t1.del_flag = 0 and t1.typeu != 7  and ${from1} !=  '${from_id}')
										) as all_count,
					(SELECT count(*)
										FROM ${dbTable} AS t1
										WHERE ((t1.${to1} = '${from_id}' or t1.${from1} = '${from_id}') and (t1.is_read is null  or t1.is_read = 0) and t1.del_flag = 0  and ${from1} !=  '${from_id}')
									)  as unread_count,
					(SELECT count(*)
										FROM ${dbTable} AS t1
										WHERE ((t1.${to1} = '${from_id}' or t1.${from1} = '${from_id}') and t1.typeu != 7 and (t1.gid = 0 or t1.gid = null) and (t1.is_read is null  or t1.is_read = 0) and t1.del_flag = 0  and ${from1} !=  '${from_id}')
									)  as single_count,
					(SELECT count(*) 
										FROM ${dbTable} AS t1
										WHERE ((t1.${to1} = '${from_id}') and t1.typeu != 7 and t1.gid != 0  and (t1.is_read is null  or t1.is_read = 0) and t1.del_flag = 0)
											) as group_count`;
				// GROUP BY t2.relatedId
				 // or t1.${from1} = '${from_id}'
				// console.log(sql);
			}

			return new Promise((resolve, reject) => {
				// 表格查询数据  执行查询的SQL语句
				plus.sqlite.selectSql({
					name: this.dbName,
					sql: sql,
					success(e) {
						resolve(e);
					},
					fail(e) {
						reject(e);
					}
				})
			})
		} else {
			return new Promise((resolve, reject) => {
				reject("错误查询")
			});
		}
	},



	finsTableValue(dbTable, from1, from_id, to1, to_id) {
		if (dbTable !== undefined) {

			if (from_id != undefined && to_id != undefined) {
				// 一个检索条件
				var sql =
					`SELECT * FROM ${dbTable} WHERE (${from1} = '${from_id}' AND ${to1} = '${to_id}') or (${from1} = '${to_id}' AND ${to1} = '${from_id}')`;
			} else if (from_id != undefined) {
				// 一个检索条件
				var sql =
					`SELECT * FROM ${dbTable} WHERE (${from1} = '${from_id}' )`;
			} else {
				var sql = `SELECT * FROM ${dbTable} `;

			}

			return new Promise((resolve, reject) => {
				// 表格查询数据  执行查询的SQL语句
				plus.sqlite.selectSql({
					name: this.dbName,
					sql: sql,
					success(e) {
						resolve(e[0]);
					},
					fail(e) {
						reject(e);
					}
				})
			})
		} else {
			return "错误查询";
		}
	},

	// 删除表里的数据 sql:'DELETE FROM dbTable WHERE lname = 'lvalue''
	// 删除 DELETE FROM 、 dbTable 是表名、 WHERE 查找条件 lname,lvalue 是查询条件的列名和列值
	deleteTableData(dbTable, lname, lvalue, ww, ee) {
		if (dbTable !== undefined) {
			if (lname == undefined) {
				var sql = `DELETE FROM ${dbTable}`;
			} else {
				if (ww !== undefined) {
					// 两个检索条件
					var sql = `DELETE FROM ${dbTable} WHERE ${lname} = '${lvalue}' AND ${ww} = '${ee}'`;
				} else {
					// 一个检索条件
					var sql = `DELETE FROM ${dbTable} WHERE ${lname} = '${lvalue}'`;
				}
			}
			return new Promise((resolve, reject) => {
				// 删除表数据
				plus.sqlite.executeSql({
					name: this.dbName,
					sql: sql,
					success(e) {
						resolve(e);
					},
					fail(e) {
						reject(e);
					}
				})
			})
		} else {
			return new Promise((resolve, reject) => {
				reject("错误删除")
			});
		}
	},
	deleteFriendTableData(dbTable) {
		if (dbTable !== undefined) {
			if (lname == undefined) {
				var sql = `DELETE FROM ${dbTable}`;
			} else {
				if (ww !== undefined) {
					// 两个检索条件
					var sql = `DELETE FROM ${dbTable} WHERE ${lname} = '${lvalue}' AND ${ww} = '${ee}'`;
				} else {
					// 一个检索条件
					var sql = `DELETE FROM ${dbTable} WHERE ${lname} = '${lvalue}'`;
				}
			}
			return new Promise((resolve, reject) => {
				// 删除表数据
				plus.sqlite.executeSql({
					name: this.dbName,
					sql: sql,
					success(e) {
						resolve(e);
					},
					fail(e) {
						reject(e);
					}
				})
			})
		} else {
			return new Promise((resolve, reject) => {
				reject("错误删除")
			});
		}
	},
	// 修改数据表里的数据 sql:"UPDATE dbTable SET 列名 = '列值',列名 = '列值' WHERE lname = 'lvalue'"
	// 修改 UPDATE 、 dbTable 是表名, data: 要修改的列名=修改后列值, lname,lvalue 是查询条件的列名和列值
	updateTableData(dbTable, data, where) {
		var sql = '';
		if (where == undefined) {
			console.log('sql');
			sql = `UPDATE ${dbTable} SET ${data}`;
		} else {
			sql =
				`UPDATE ${dbTable} SET ${data} WHERE ${where}`;
		}
		// console.log(sql);
		// WHERE 前面是要修改的列名、列值，后面是条件的列名、列值
		return new Promise((resolve, reject) => {
			// 修改表数据
			plus.sqlite.executeSql({
				name: this.dbName,
				sql: sql,
				success(e) {
					resolve(e);
				},
				fail(e) {
					reject(e);
				}
			})
		})
	},

	// 获取指定数据条数  sql:"SELECT * FROM dbTable ORDER BY 'id' DESC LIMIT 15 OFFSET 'num'"
	// dbTable 表名, ORDER BY 代表排序默认正序, id 是排序的条件 DESC 代表倒序，从最后一条数据开始拿
	// LIMIT 15 OFFSET '${num}',这句的意思是跳过 num 条拿 15 条数据, num 为跳过多少条数据是动态值
	// 例 初始num设为0，就从最后的数据开始拿15条，下次不拿刚获取的数据，所以可以让num为15，这样就能一步一步的拿完所有的数据
	pullSQL(dbTable, id, num) {
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: this.dbName,
				sql: `SELECT * FROM ${dbTable} ORDER BY '${id}' DESC LIMIT 15 OFFSET '${num}'`,
				success(e) {
					resolve(e);
				},
				fail(e) {
					reject(e);
				}
			})
		})
	},


	// 查询表是否存在
	isTable(dbTable) {
		return new Promise((resolve, reject) => {
			plus.sqlite.selectSql({
				name: this.dbName,
				sql: `select count(*) as isTable FROM sqlite_master where type='table' and name='${dbTable}'`,
				success(e) {
					resolve(e[0].isTable ? true : false);
				},
				fail(e) {
					reject(e);
				}
			})
		})
	}
}